﻿namespace MicroCloud.Database
{
    #region "数据库触发器处理器基类"
    /// <summary>
    /// 数据库触发器处理器基类
    /// </summary>
    public abstract class TriggerHandlerBase : DataBaseHandler, ITriggerHandler
    {
        #region "构造函数"
        #region "初始化一个数据库触发器处理器基类的新实例"
        /// <summary>
        /// 初始化一个数据库触发器处理器基类 <see cref="TriggerHandlerBase"/> 的新实例
        /// </summary>
        /// <param name="serviceProvider">服务提供者</param>
        protected TriggerHandlerBase(IServiceProvider serviceProvider)
            : base(serviceProvider)
        { }
        #endregion

        #endregion

        #region "方法"
        #region "初始化"
        /// <summary>
        /// 初始化
        /// </summary>
        /// <param name="dbContextType">数据上下文类型</param>
        public void Initialize(Type dbContextType)
        {
            ServiceProvider.ExecuteScopedWork(provider =>
            {
                string[] exceptNames = { "ServiceProvider", "Logger" };
                var requireSynch = this.CheckSyncByHash(provider, Logger, exceptNames, GetType(), GetType().GetDescription(), $"初始化{GetType().GetDescription()}");

                if (!requireSynch)
                {
                    Logger.LogInformation("同步触发器 {type} 到数据库时，验证数据签名与上次相同，跳过同步。", GetType());
                    return;
                }
                SyncToDatabase(dbContextType, provider);
                Logger.LogInformation("同步触发器 {type} 到数据库", GetType());
            });
        }
        #endregion
        #region "获取触发器名称"
        /// <summary>
        /// 获取触发器名称
        /// </summary>
        /// <param name="dbContext">数据上下文</param>
        /// <returns>触发器名称</returns>
        public virtual string GetTriggerName(IDbContext dbContext)
        {
            var name = string.Empty;
            bool? exist = CheckExist(dbContext);
            if (exist != true)
            {
                return name;
            }
            var databaseType = GetDbContextOptions(dbContext).DatabaseType;
            name = GetName(databaseType);
            return name;
        }
        #endregion

        #endregion

        #region "受保护的方法"
        #region "同步至数据库"
        /// <summary>
        /// 同步至数据库
        /// </summary>
        /// <param name="dbContextType">数据上下文类型</param>
        /// <param name="provider">服务提供者</param>
        protected virtual void SyncToDatabase(Type dbContextType, IServiceProvider provider)
        {
            if (!DbContextOptions.Any(o => o.Key == dbContextType))
            {
                return;
            }
            var dbContextOption = DbContextOptions[dbContextType];
            IUnitOfWork unitOfWork = provider.GetUnitOfWork(true);
            var dbContext = unitOfWork.GetDbContext(dbContextType);
            if (Deleted)
            {
                //执行删除
                Delete(dbContext);
                Logger.LogDebug("数据上下文 {dbContextName} 删除触发器 {name}", dbContextType.Name, GetName(dbContextOption.DatabaseType));
            }
            else
            {
                //执行创建或者更新
                var exist = CheckExist(dbContext);
                if (exist == null)
                {
                    Logger.LogWarning("数据上下文 {dbContextName} 不支持同步触发器 {name}", dbContextType.Name, GetName(dbContextOption.DatabaseType));
                }
                else if (exist == true)
                {
                    //执行更新
                    Update(dbContext);
                    Logger.LogDebug("数据上下文 {dbContextName} 更新触发器 {name}", dbContextType.Name, GetName(dbContextOption.DatabaseType));
                }
                else if (exist == false)
                {
                    //执行创建
                    Create(dbContext);
                    Logger.LogDebug("数据上下文 {dbContextName} 新增触发器 {name}", dbContextType.Name, GetName(dbContextOption.DatabaseType));
                }
            }

            unitOfWork.Commit();
        }
        #endregion

        #region "检查是否存在"
        /// <summary>
        /// 检查是否存在
        /// </summary>
        /// <param name="dbContext">数据上下文</param>
        /// <returns>null：不支持、ture：已存在、false：不存在</returns>
        protected virtual bool? CheckExist(IDbContext dbContext)
        {
            bool? exist = null;
            var databaseType = GetDbContextOptions(dbContext).DatabaseType;
            var sqlString = GetExistScript(databaseType);
            if (sqlString.IsMissing())
            {
                return exist;
            }
            var result = dbContext.SqlQueryToDataTable(sqlString);
            exist = result?.Rows?.Count > 0;
            return exist;
        }
        #endregion
        #region "创建"
        /// <summary>
        /// 创建
        /// </summary>
        /// <param name="dbContext">数据上下文</param>
        /// <returns>null：不支持、ture：成功、false：失败</returns>
        protected virtual bool? Create(IDbContext dbContext)
        {
            bool? flag = CheckExist(dbContext);
            if (flag == null)
            {
                return flag;
            }
            var databaseType = GetDbContextOptions(dbContext).DatabaseType;
            var sqlString = GetCreateScript(databaseType);
            if (sqlString.IsMissing())
            {
                return flag;
            }
            try
            {
                if (flag == false)
                {
                    dbContext.ExecuteSqlRaw(sqlString);
                }
                flag = true;
            }
            catch (System.Exception ex)
            {
                flag = false;
                Logger.LogError(ex, @"数据库触发器在上下文 {dbContext} 中执行创建脚本错误
脚本信息：
{sqlString}", dbContext, sqlString);
                //throw new MicroCloudException(message, ex);
            }
            return flag;
        }
        #endregion
        #region "更新"
        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="dbContext">数据上下文</param>
        /// <returns>null：不支持、ture：成功、false：失败</returns>
        protected virtual bool? Update(IDbContext dbContext)
        {
            bool? flag = CheckExist(dbContext);
            if (flag == null)
            {
                return flag;
            }
            else if (flag == false)
            {
                flag = Create(dbContext);
            }
            else
            {
                var databaseType = GetDbContextOptions(dbContext).DatabaseType;
                var sqlString = GetUpdateScript(databaseType);
                if (sqlString.IsMissing())
                {
                    return flag;
                }
                try
                {
                    if (flag == true)
                    {
                        dbContext.ExecuteSqlRaw(sqlString);
                    }
                    flag = true;
                }
                catch (System.Exception ex)
                {
                    flag = false;
                    Logger.LogError(ex, @"数据库触发器在上下文 {dbContext} 中执行更改脚本错误
脚本信息：
{sqlString}", dbContext, sqlString);
                    //throw new MicroCloudException(message, ex);
                }
            }
            return flag;
        }
        #endregion
        #region "删除"
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="dbContext">数据库对象信息</param>
        /// <returns>null：不支持、ture：成功、false：失败</returns>
        protected virtual bool? Delete(IDbContext dbContext)
        {
            bool? flag = CheckExist(dbContext);
            if (flag == null)
            {
                return flag;
            }
            var databaseType = GetDbContextOptions(dbContext).DatabaseType;
            var sqlString = GetDeleteScript(databaseType);
            if (sqlString.IsMissing())
            {
                return flag;
            }
            try
            {
                if (flag == true)
                {
                    dbContext.ExecuteSqlRaw(sqlString);
                }
                flag = true;
            }
            catch (System.Exception ex)
            {
                flag = false;
                Logger.LogError(ex, @"数据库触发器在上下文 {dbContext} 中执行删除脚本错误
脚本信息：
{sqlString}", dbContext, sqlString);
                //throw new MicroCloudException(message, ex);
            }
            return flag;
        }
        #endregion

        #endregion

        #region "重新方法-受保护"
        #region "获取是否存在脚本"
        /// <summary>
        /// 获取是否存在脚本
        /// </summary>
        /// <param name="databaseType">数据库类型</param>
        protected override string GetExistScript(DatabaseType databaseType)
        {
            var sqlString = "";
            var name = GetName(databaseType);
            if (name.IsMissing())
            {
                return sqlString;
            }
            switch (databaseType)
            {
                case DatabaseType.Sqlite:
                    break;
                case DatabaseType.MySql:
                    sqlString = @$"SHOW TRIGGERS STATUS WHERE `name` = '{name}';";
                    //sqlString = @$"SELECT TRIGGER_NAME FROM information_schema.triggers WHERE TRIGGER_NAME = '{name}' AND EVENT_OBJECT_TABLE = '{info.TableName}';";
                    break;
                case DatabaseType.SqlServer:
                    sqlString = @$"SELECT name FROM SYSOBJECTS WHERE id = OBJECT_ID('{name}') AND OBJECTPROPERTY(id, 'IsTrigger') = 1;";
                    break;
                case DatabaseType.PostgreSql:
                    sqlString = $@"SELECT tgname FROM pg_trigger WHERE tgname = '{name}';";
                    break;
                case DatabaseType.Oracle:
                    sqlString = @$"SELECT TRIGGER_NAME FROM dba_triggers WHERE TRIGGER_NAME = '{name}';";
                    break;
            }
            return sqlString;
        }
        #endregion
        #region "获取删除脚本"
        /// <summary>
        /// 获取删除脚本
        /// </summary>
        /// <param name="databaseType">数据库类型</param>
        protected override string GetDeleteScript(DatabaseType databaseType)
        {
            var sqlString = "";
            var name = GetName(databaseType);
            if (name.IsMissing())
            {
                return sqlString;
            }
            switch (databaseType)
            {
                case DatabaseType.Sqlite:
                    break;
                case DatabaseType.MySql:
                    sqlString = @$"DROP TRIGGER IF EXISTS {name}";
                    break;
                case DatabaseType.SqlServer:
                    sqlString = @$"DROP TRIGGER IF EXISTS {name}";
                    break;
                case DatabaseType.PostgreSql:
                    sqlString = @$"drop trigger if exists {name}";
                    break;
                case DatabaseType.Oracle:
                    sqlString = @$"DROP TRIGGER IF EXISTS {name}";
                    break;
            }
            return sqlString;
        }
        #endregion

        #endregion

    }
    #endregion

}
